package test;

import java.sql.*;
import java.io.*;




/**
 * 
 * @author shefali
 * In all the functions of this class a connection to the database is achieved via a DriverManager class
 * Once the JDBC drivers are loaded, getConnection method is applied to the Driver Manager class using the
 * Appropriate url and username, password
 * 
 * Prepared statement is used to send SQL statements to the database server since it is more efficient than the
 * Statement object
 * The ResultSet class is used to access the table of data generated by executing the sql queries
 *
 */
public class SpeakerDataAccess {
	/**
	 * 
	 * @param fName
	 * @param lName
	 * @param title
	 * @param organization
	 * @param email
	 * @param address
	 * @param city
	 * @param state
	 * @param country
	 * @param zip
	 * @param phone
	 * @param fax
	 * @return speakerId
	 * @throws SQLException
	 * @throws IOException
	 * Saves all the personal details of the Speaker when the speaker submits a proposal for the first time.
	 * A returning speaker is identified by email id i.e if the email id already exists in the database that means the speaker has at some point
	 * submitted a proposal for some tutor/Mentor Conference.In this case the speakerId which was generated the first time the user registered 
	 * is fetched from the database and the corresponding column attributes are updated.
	 * The Speaker id is generated for a new speaker using ORACLE sequences and the other relevant data is inserted in the Speaker table
	 * The function returns the already existing or the newly generated speakerId
	 */
	
	
		public int saveSpeakerDetails(String fName, String lName, String title, String organization, String email, String address, String city, String state, String country, int zip, String phone, String fax) throws SQLException, IOException 
		{
			PreparedStatement pstmt = null;
			Statement stmt = null;
			ResultSet rset = null;
			Connection conn = null;
			int speakerId = 0;
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				String user, pass;
				user = "skakodkar";
				pass = "p73505";
				conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/" + pass + "@oraserver.cs.luc.edu:1521:cs");
				String query = "Select speakerId" + " from speaker " + " where email = " + "'" + email + "'";
				stmt = conn.createStatement();
				rset = stmt.executeQuery(query);
				
				if(rset.next())
				{
					 speakerId = rset.getInt(1);
					
					String query1 = "Update speaker set fName = ?, lName= ?, title = ?, company = ?," + "address = ?, city = ?, state = ?, country = ?, zipcode = ?, phone = ?, fax =? where speakerid = ?";
					pstmt = conn.prepareStatement(query1);
					pstmt.setString(1,fName);
					pstmt.setString(2,lName);
					pstmt.setString(3,title);
					pstmt.setString(4,organization);
					pstmt.setString(5,address);
					pstmt.setString(6,city);
					pstmt.setString(7,state );
					pstmt.setString(8,country);
					pstmt.setInt(9,zip);
					pstmt.setString(10,phone);
					pstmt.setString(11,fax);
					pstmt.setInt(12,speakerId);
					pstmt.executeUpdate();
			}
				else
					{

					String query2 = "Insert into speaker values(sequence_speakerId.nextval,?,?,?,?,?,?,?,?,?,?,?,?)";
			
			pstmt = conn.prepareStatement(query2);
			pstmt.setString(1,fName);
			pstmt.setString(2,lName);
			pstmt.setString(3,title);
			pstmt.setString(4,organization);
			pstmt.setString(5,email);
			pstmt.setString(6,address);
			pstmt.setString(7,city);
			pstmt.setString(8,state);
			pstmt.setString(9,country);
			pstmt.setInt(10,zip);
			pstmt.setString(11,phone);
			pstmt.setString(12,fax);
					
			int x = pstmt.executeUpdate();
			 String query3 = "select speakerId from speaker where email = ?";
				pstmt = conn.prepareStatement(query3);
				pstmt.setString(1,email);
				
				rset = pstmt.executeQuery();
				}
				while(rset.next())
				{
					Speaker sp = new Speaker();
					sp.setSpeakerId (rset.getInt(1));
					speakerId = sp.getSpeakerId();
				}
					}
					catch(Exception e) {
						e.printStackTrace();
					} finally {
				conn.close();
			}
			return speakerId;
		}

		/**
		 * 
		 * @param wTitle
		 * @param speakerId
		 * @param description
		 * @param levelOfExperience
		 * @param audioVisualReq
		 * @throws SQLException
		 * @throws IOException
		 * 
		 * The details in the form specific to the proposal that the speaker wishes to submit are stored in the Proposal table in the 
		 * database which has the speakerId generated above as a foreign key
		 */
		
		
		public void saveProposalDetails(String wTitle, int speakerId, String description, String levelOfExperience, String audioVisualReq)throws SQLException, IOException
		{
		
			PreparedStatement pstmt = null;
			ResultSet rset = null;
			Connection conn = null;
			
			try {
				
				Class.forName("oracle.jdbc.driver.OracleDriver");
				String user, pass;
				user = "skakodkar";
				pass = "p73505";
				conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/" + pass + "@oraserver.cs.luc.edu:1521:cs");
				
						
			String query2 = "Insert into proposal(wTitle, speakerId, description,levelOfExperience,audioVisualReq) values(?,?,?,?,?)";
			
			pstmt = conn.prepareStatement(query2);
			pstmt.setString(1,wTitle);
			pstmt.setInt(2,speakerId);
			pstmt.setString(3,description);
			pstmt.setString(4,levelOfExperience);
			pstmt.setString(5,audioVisualReq);	
			 pstmt.executeUpdate();
			
			
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				conn.close();
			}
			
		}
			

}
